﻿using System;
using System.Data;
using System.Data.OleDb;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.ComponentModel;
using System.Web;
namespace DAO
{
    public class dataProvider
    {
        private static string _myConnectionString;
        private OleDbConnection myConnection;

        public static string MyConnectionString
        {
            get { return dataProvider._myConnectionString; }
            set { dataProvider._myConnectionString = value; }
        }

        private OleDbConnection myCon;
        //private string strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + @"D:\HCMC_Service_Search_DB_Intergrated_.accdb" + ";Persist Security Info=False;";
        
        public dataProvider()
        {
            //this.myCon = new OleDbConnection(this.strCon);
            this.myCon = new OleDbConnection(MyConnectionString);
        }
        
        //public string getStrCon(){
        //    return this.strCon;
        //}

        public DataTable executeQuery(string strQuery)
        {
            DataTable dt = new DataTable();
            try
            {
                myCon.Open();

                OleDbDataAdapter da = new OleDbDataAdapter(strQuery, myCon);

                da.Fill(dt);
            }
            catch (Exception ex)
            {
                throw new Exception("Loi khi thuc thi lenh SQL: " + ex.Message);
            }
            finally
            {
                myCon.Close();
            }

            return dt;
        }

        public DataTable executeStoreProc(string storeProcName, IList<string> arrParameterName, ArrayList arrParameterValue)
        {
            DataTable resTable = null;

            try
            {
                myCon.Open();

                OleDbCommand cmd = new OleDbCommand();
                cmd.CommandText = storeProcName;
                cmd.CommandType = CommandType.StoredProcedure;

                //Lay so parameter
                int N = arrParameterName.Count;
                for (int i = 0; i < N; i++)
                {
                    OleDbParameter sqlParam = new OleDbParameter(arrParameterName[i], arrParameterValue[i]);
                    cmd.Parameters.Add(sqlParam);
                }

                OleDbDataAdapter adapter = new OleDbDataAdapter(cmd);
                adapter.Fill(resTable);

                myCon.Close();
            }
            catch (Exception ex)
            {
                throw new Exception("Loi khi thuc thi store procedure: " + ex.Message);
            }

            return resTable;
        }
        public int excuteScalar(string strSQL)
        {
            int val;
            try
            {
                // myCon.Open();
                OleDbCommand cmd = new OleDbCommand(strSQL, myCon);
                cmd.Connection.Open();
                val = Convert.ToInt32(cmd.ExecuteScalar());
                cmd.Connection.Close();
                //myCon.Close();
            }
            catch (Exception ex)
            {
                throw new Exception("Loi khi thuc thi store procedure: " + ex.Message);
            }
            return val;
        }
        // get Number then return a strning type
        // ex : reutrun a 2 number 2.ToStrong ==> "2"
        public string excuteScalarString(string strSQL)
        {
            string val;
            try
            {
                // myCon.Open();
                OleDbCommand cmd = new OleDbCommand(strSQL, myCon);
                cmd.Connection.Open();
                val = cmd.ExecuteScalar().ToString();
                cmd.Connection.Close();
                //myCon.Close();
            }
            catch (Exception ex)
            {
                throw new Exception("Loi khi thuc thi store procedure: " + ex.Message);
            }
            return val;
        }
        // getValue od one Row in Table. Value at this row is a string type
        public string getStrByString(string strQuery, string Filedname)
        {
            DataTable dt = new DataTable();
            string val = "";
            try
            {
                myCon.Open();

                OleDbDataAdapter da = new OleDbDataAdapter(strQuery, myCon);

                da.Fill(dt);
            }
            catch (Exception ex)
            {
                throw new Exception("Loi khi thuc thi lenh SQL: " + ex.Message);
            }
            finally
            {
                myCon.Close();
            }
            // Khi Table có dữ liệu
            if (dt.Rows.Count > 0)
                val = dt.Rows[0][Filedname].ToString();
            return val;
        }
        public bool executeNonQuery(string strSQL)
        {
            bool flag = false;
            try
            {
                myCon.Open();
                Console.WriteLine(strSQL);
                OleDbCommand cmd = new OleDbCommand(strSQL, myCon);
                cmd.ExecuteNonQuery();
                flag = true;
            }
            catch (Exception ex)
            {
                throw new Exception("Loi khi thuc thi lenh SQL: " + ex.Message);

            }
            finally
            {
                myCon.Close();
            }
            return flag;
        }
        public string getLastID(string nameTable, string nameFiled)
        {
            DataTable dt = new DataTable();
            string strSQL = "SELECT TOP 1 " + nameFiled + " FROM " + nameTable + " ORDER BY " + nameFiled + " DESC";
            dt = executeQuery(strSQL);
            if (dt.Rows.Count > 0)
                return dt.Rows[0][nameFiled].ToString();
            return "";
        }
        public string nextID(string lastID, string prefixID)
        {
            if (lastID != "")
            {
                int nextID = int.Parse(lastID.Remove(0, prefixID.Length)) + 0;
                int lengthNumerID = lastID.Length - prefixID.Length;
                string zeroNumber = "";
                for (int i = 1; i <= lengthNumerID; i++)
                {
                    if (nextID < Math.Pow(10, i))
                    {
                        for (int j = 1; j <= lengthNumerID - i; i++)
                        {
                            zeroNumber += "0";
                        }
                        return prefixID + zeroNumber + nextID.ToString();
                    }
                }
                return prefixID + nextID;
            }
            else
                return prefixID + "00000001";
        }
        public bool executeReader(string sql)
        {
            bool flag = false;
            try
            {
                myCon.Open();
                OleDbCommand cmd = new OleDbCommand(sql, myCon);
                OleDbDataReader dar = cmd.ExecuteReader();
                if (dar.Read())
                {
                    flag = true;
                }
                else
                {
                    flag = false;
                }
                dar.Close();
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
            finally
            {
                myCon.Close();
            }
            return flag;
        }

    }
}